/**
 * 
 */
package soa.security.mgr.service.impl;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.BooleanUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.ldap.core.LdapTemplate;
import org.springframework.ldap.query.SearchScope;
import org.springframework.stereotype.Service;

import soa.security.Constants;
import soa.security.dao.RegionDao;
import soa.security.domain.Account;
import soa.security.domain.Applsystem;
import soa.security.domain.Department;
import soa.security.domain.Group;
import soa.security.domain.Organization;
import soa.security.domain.Person;
import soa.security.domain.Region;
import soa.security.domain.Role;
import soa.security.mgr.service.AccountMgrService;
import soa.security.mgr.service.AppService;
import soa.security.mgr.service.ApplsystemMgrService;
import soa.security.mgr.service.DepartmentMgrService;
import soa.security.mgr.service.GroupMgrService;
import soa.security.mgr.service.OrganizationMgrService;
import soa.security.mgr.service.PersonMgrService;
import soa.security.mgr.service.PositionMgrService;
import soa.security.mgr.service.RegionMgrService;
import soa.security.mgr.service.RoleMgrService;

/**
 * @author Cre.Gu
 *
 */
@Service
@PropertySource(value = { "classpath:/ldap.properties" })
public class AppServiceImpl implements AppService {
	protected final org.slf4j.Logger log = org.slf4j.LoggerFactory
			.getLogger(this.getClass());

	@Value("#{ environment['ldap.app.forceinit'] }")
	private String forceInit;

	@Resource
	private RegionDao regionDao;
	@Resource
	AccountMgrService accountMgrService;
	@Resource
	ApplsystemMgrService applsystemMgrService;
	@Resource
	DepartmentMgrService departmentMgrService;
	@Resource
	OrganizationMgrService organizationMgrService;
	@Resource
	PersonMgrService personMgrService;
	@Resource
	PositionMgrService positionMgrService;
	@Resource
	GroupMgrService groupMgrService;
	@Resource
	RoleMgrService roleMgrService;
	@Resource
	RegionMgrService regionMgrService;
	@Resource
	private LdapTemplate ldapTemplate;

	private Region hzsbj;
	private Organization gwyj;
	String gwyjId;

	/*
	 * (non-Javadoc)
	 * 
	 * @see soa.security.mgr.service.AppService#initData()
	 */
	@Override
	public void initData() {
		boolean hasRegion = hasRegion();
		
		/***** 测试 *****/
		if (hasRegion && forceInit())
			deleteAll();
		
		if (!hasRegion) {
			/*initRegion();
			initSuperAccount();
			initOrganization();
			initAccount();			
			initDepartment();
			initPerson();
			initApplsystem();
			initComplex();*/
			
			try {
				initRegionExcel("E:\\111\\region.xlsx");
				initOrganizationExcel("E:\\111\\org.xlsx");
				//initDepartmentExcel("E:\\dep.xlsx");
				initSuperAccount();
				initPersonExcel("E:\\111\\person.xlsx");
				initAccountExcel("E:\\111\\account.xlsx");
			} catch (InvalidFormatException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	void initRegion() {
		Region root = regionMgrService.initRoot();

		Region zjs = new Region("RE0001", "浙江省","33");
		regionMgrService.addRegion(root.getDn().toString(), zjs);
		
		Region re = new Region("RE0002", "省本级","3300");
		regionMgrService.addRegion(zjs.getDn().toString(), re);

		Region re2 = new Region("RE0003", "杭州市","3301");
		regionMgrService.addRegion(zjs.getDn().toString(), re2);
		
		Region re3 = new Region("RE0006", "宁波市","3302");
		regionMgrService.addRegion(zjs.getDn().toString(), re3);

		hzsbj = new Region("RE0004", "杭州市本级","330100");
		regionMgrService.addRegion(re2.getDn().toString(), hzsbj);
		
		Region re22 = new Region("RE0005", "西湖区","330106");
		regionMgrService.addRegion(re2.getDn().toString(), re22);
	}

	void initSuperAccount() {
		accountMgrService.initializeSuperAccount();
	}

	void initAccount() {
		for (int i = 1; i < 3; i++) {
			Account a = new Account("test" + i, Constants.ACCOUNT_INIT_PWD);
			accountMgrService.addAccount(gwyjId, a);
		}
	}

	void initOrganization() {
		Organization root = new Organization("OR0001", "人社厅", "rst", "1001");
		root.setCode("1001");
		organizationMgrService.addOrganization(hzsbj.getDn().toString(), root);

		gwyj = new Organization("OR0002", "公务员局", "gwyj", "100101");
		gwyj.setCode("100101");
		organizationMgrService.addSubOrganization(root.getBid(), gwyj);
		gwyjId = gwyj.getBid();

		for (int i = 1; i < 3; i++) {
			Organization organization = new Organization("OR000" + (2 + i),
					"人社厅子机构" + i, "rstzjg" + i, "10010" + (i + 1));
			organization.setCode("10010" + (i + 1));
			organizationMgrService.addSubOrganization(root.getBid(),
					organization);
		}
	}
	
	void initApplsystem() {
		for (int i = 1; i < 3; i++) {
			Applsystem applsystem = new Applsystem("AP000" + i, "测试应用系统0" + i, "\\logo\\zjlogo.png1447313513599", "http://www.baidu.com", "系统描述0" + i);
			applsystemMgrService.addApplsystem(gwyjId, applsystem);
		}
	}

	void initDepartment() {
		departmentMgrService.addDepartment(gwyjId,
				Department.build("DE0001", "办公室"));
		departmentMgrService.addDepartment(gwyjId,
				Department.build("DE0002", "宣传部"));

		departmentMgrService.addDepartment(gwyjId,
				Department.build("DE0003", "宣传部子部", "DE0002"));
	}

	void initPerson() {
		personMgrService.addPerson(gwyjId, Person.build("PE0001", "人员1"));
		personMgrService.addPerson(gwyjId, Person.build("PE0002", "人员2"));
		personMgrService.addPerson(gwyjId, Person.build("PE0003", "人员3"));
	}

	void initComplex() {
		groupMgrService.addGroup(gwyjId, Group.build("GR0001", "组1"));
		groupMgrService.addGroup(gwyjId, Group.build("GR0002", "组2"));
		roleMgrService.addRole(gwyjId, Role.build("RO0001", "角色1"));
		roleMgrService.addRole(gwyjId, Role.build("RO0002", "角色2"));

		departmentMgrService.addGroups(gwyjId, "DE0001", "GR0001,GR0002");
		departmentMgrService.addRoles(gwyjId, "DE0001", "RO0001,RO0002");

		groupMgrService.addPersons(gwyjId, "GR0001", "PE0001,PE0002");
		roleMgrService.addPersons(gwyjId, "RO0001", "PE0001,PE0002");

		groupMgrService.addSubGroups(gwyjId, "GR0001", "GR0002");
		roleMgrService.addSubRoles(gwyjId, "RO0001", "RO0002");
		
		accountMgrService.addApplsystem(gwyjId, "test1@GWYJ", "AP0001,AP0002");
		accountMgrService.addApplsystem(gwyjId, "test2@GWYJ", "AP0001");
	}

	private boolean hasRegion() {
		return regionDao.allRegions(null, SearchScope.ONELEVEL).size() > 0;
	}

	private boolean forceInit() {
		return BooleanUtils.toBoolean(forceInit);
	}

	void deleteAll() {
		// regionMgrService.deleteAllEntries();
		// ldapTemplate.unbind("dc=zjs", true);
	}
	
	/**
	 * 初始化地区
	 * 
	 * @param file
	 * @throws IOException
	 * @throws InvalidFormatException
	 * @author CY
	 */
	void initRegionExcel(String file) throws InvalidFormatException, IOException {
		//初始化根目录
		System.out.println("初始化根目录开始……");
		Region regionRoot = regionMgrService.initRoot();
		System.out.println("初始化根目录结束……");
		
		List<Region> list = readRegionExcel(file);		
		for (int i = 0; i < list.size(); i++) {
			Region region = list.get(i);
			region.setBid("RE" + String.format("%04d", i + 1));
			
			Region re = new Region();
			
			//地区编码
			String code = region.getCode();
			
			//通过当前地区编码获取父地区编码，继而获取父地区对象
			if ("0000000000".equals(code.substring(2))) {
				regionMgrService.addRegion(regionRoot.getDn().toString(), region);
			} else {					
				String parentCode = "";
				if ("00000000".equals(code.substring(4))) {
					parentCode = code.substring(0, 2) + "0000000000";
				} else if("000000".equals(code.substring(6))) {
					parentCode = code.substring(0, 4) + "00000000";
				}
				re = regionMgrService.existByCode(parentCode);
				regionMgrService.addRegion(re.getDn().toString(), region);
			}
			
			System.out.println("new region " + i + " : " + region.getBid());
		}
	}

	/**
	 * 初始化机构
	 * @param file
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	void initOrganizationExcel(String file) throws InvalidFormatException, IOException {

		List<Organization> list = readOrganizationExcel(file);
		
		for (int i = 0; i < list.size(); i++) {
			Organization organization = list.get(i);
			
			Organization org = new Organization();
			
			//机构编码长度
			int length = organization.getCode().length();
			if (length == 2) {
				//获取所属地区数据
				Region region = new Region();
				region = regionMgrService.existByCode(organization.getRegionCode());
				organizationMgrService.addOrganization(region.getDn().toString(), organization);
			} else {
				String parentCode = "";
				if (length == 4) {
					parentCode = organization.getCode().substring(0, 2);
				} else if(length == 6) {
					parentCode = organization.getCode().substring(0, 4);
				} else if(length == 8) {
					parentCode = organization.getCode().substring(0, 6);
				}
				org = organizationMgrService.existByCode(parentCode);
				organizationMgrService.addSubOrganization(org.getBid(), organization);
			}
			System.out.println("new organization " + i);
		}
	}
	
	void initDepartmentExcel(String file) throws InvalidFormatException, IOException {
		List<Department> list = readDepartmentExcel(file);
		for (int i = 0; i < list.size(); i++) {
			Department department = list.get(i);
			
			System.out.println("parentCode : " + department.getOrganization().getCode());
			System.out.println("gradeCode : " + department.getGradeCode());
			
			//根据父编码获取机构编码
			Organization org = new Organization();
			org = organizationMgrService.existByCode(department.getOrganization().getCode());
			
			//如果org是null，说明该父code不是机构，是一个部门code
			if (org == null) {				
				
				//首先根据上一级的code获取所属的机构
				String code = department.getOrganization().getCode();
				org = organizationMgrService.existByCode(code.substring(0, code.length() - 2));
				
				//然后根据该部门code获取上级部门code，再据此获取父部门（如果父部门不存在，说明没有父部门）
				Department department_parent  = departmentMgrService.existByGradeCode(department.getGradeCode().substring(0, department.getGradeCode().length() - 2));
				if (department_parent != null) {
					System.out.println("department_parent : " + department_parent.getBid());
					departmentMgrService.addDepartment(org.getBid(), Department.build("", department.getName(), department_parent.getBid(), department.getGradeCode()));
				} else {
					departmentMgrService.addDepartment(org.getBid(), Department.buildGradeCode("", department.getName(), department.getGradeCode()));
				}
			} else {
				departmentMgrService.addDepartment(org.getBid(), Department.buildGradeCode("", department.getName(), department.getGradeCode()));
			}
			System.out.println("new department " + i);
			System.out.println("-------------------------");
		}
	}
	
	/**
	 * 初始化人员
	 * @param file
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	void initPersonExcel(String file) throws InvalidFormatException, IOException {

		List<Person> list = readPersonExcel(file);
		
		for (int i = 0; i < list.size(); i++) {
			Person person = list.get(i);
			//根据机构内码，获取人员的所属机构
			Organization org = new Organization();
			org = organizationMgrService.existByOrganizationCode(person.getOrganization().getOrganizationCode());
			//本人员属于部门下面
			if (org == null) {
				//根据code获取父code，获取所属机构
				String code = person.getOrganization().getCode();
				org = organizationMgrService.existByCode(code.substring(0, code.length() - 2));
				if (org == null) {
					org = organizationMgrService.existByCode(code.substring(0, code.length() - 4));
				}
				
				//新增人员，部门关联人员
				//获取部门对象
				Department dep = departmentMgrService.existByName(person.getOrganization().getName());
				
				personMgrService.addPersonRe(org.getBid(), Person.build(person.getUid(), person.getName(), person.getMobile(), person.getEmail()), dep);
				
				
			} else {
				personMgrService.addPerson(org.getBid(), Person.build(person.getUid(), person.getName(), person.getMobile(), person.getEmail()));
			}
			System.out.println("new person " + i);
		}
	}
	
	/**
	 * 初始化人员(外网)
	 * @param file
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	void initPersonExcelNetwork(String file) throws InvalidFormatException, IOException {

		List<Person> list = readPersonExcelNetwork(file);
		
		for (int i = 0; i < list.size(); i++) {
			Person person = list.get(i);
			//根据机构内码，获取人员的所属机构
			Organization org = new Organization();
			org = organizationMgrService.existByOrganizationCode(person.getOrganization().getOrganizationCode());
			
			personMgrService.addPerson(org.getBid(), Person.build(person.getUid(), person.getName(), person.getMobile(), person.getEmail()));
			
			System.out.println("new person " + i);
		}
	}
	
	/**
	 * 初始化账户
	 * @param file
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	void initAccountExcel(String file) throws InvalidFormatException, IOException {

		List<Account> list = readAccountExcel(file);
		
		for (int i = 0; i < list.size(); i++) {
			Account account = list.get(i);
			account.setPassword(Constants.ACCOUNT_INIT_PWD);
			
			//根据机构内码，获取人员的所属机构
			Organization org = new Organization();
			org = organizationMgrService.existByOrganizationCode(account.getOrganization().getOrganizationCode());
			
			//本人员属于部门下面
			if (org == null) {
				//根据code获取父code，获取所属机构
				String code = account.getOrganization().getCode();
				org = organizationMgrService.existByCode(code.substring(0, code.length() - 2));
				if (org == null) {
					org = organizationMgrService.existByCode(code.substring(0, code.length() - 4));
				}
			}
			
			//设置该机构下的关联人员
			if (account.getPerson() != null && account.getPerson().getName() != null) {
				List<Person> personList = personMgrService.findByOrgAndName(org.getBid(), account.getPerson().getName());
				if (personList != null && personList.size() > 0) {
					account.setPersonBid(personList.get(0).getUid());
				}
			}
			accountMgrService.addAccount(org.getBid(), account);
			System.out.println("new account " + i);
		}
	}
	
	/**
	 * 初始化账户(外网)
	 * @param file
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	void initAccountExcelNetwork(String file) throws InvalidFormatException, IOException {

		List<Account> list = readAccountExcelNew(file);
		
		for (int i = 0; i < list.size(); i++) {
			Account account = list.get(i);
			account.setPassword(Constants.ACCOUNT_INIT_PWD);
			
			//根据机构内码，获取人员的所属机构
			Organization org = new Organization();
			org = organizationMgrService.existByOrganizationCode(account.getOrganization().getOrganizationCode());
						
			//设置该机构下的关联人员
			if (account.getPerson() != null && account.getPerson().getName() != null) {
				List<Person> personList = personMgrService.findByOrgAndName(org.getBid(), account.getPerson().getName());
				if (personList != null && personList.size() > 0) {
					account.setPersonBid(personList.get(0).getUid());
				}
			}
			accountMgrService.addAccount(org.getBid(), account);
			System.out.println("new account " + i);
		}
	}
	
	/**
	 * 初始化测试系统数据
	 * @author CY
	 */
	void initApplsystemExcel() {
		for (int i = 1; i < 6; i++) {
			Applsystem applsystem = new Applsystem("AP000" + i, "应用系统0" + i, "\\logo\\zjlogo.png1447313513599", "http://www.baidu.com", "系统描述0" + i);
			applsystemMgrService.addApplsystem_region(applsystem);
			
			accountMgrService.addApplsystem("OR0003", "test1@GWYJ", "AP0001,AP0002");
			accountMgrService.addApplsystem("OR0003", "test2@GWYJ", "AP0001");
		}
	}

	/**
	 * 读取地区Excel数据
	 * 
	 * @param file
	 * @return List<Region>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Region> readRegionExcel(String file) throws InvalidFormatException, IOException {
		List<Region> list = new ArrayList<Region>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Region bean = new Region();
			// 遍历行单元格
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);

				switch (j) {
				case 0:
					bean.setName(cellValue);
					break;
				case 1:
					if (cellValue.indexOf(".") == -1) {
						bean.setCode(cellValue);
					} else {
						bean.setCode(cellValue.substring(0, cellValue.indexOf(".")));
					}
					break;
				default:
					System.out.println("region cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}

	/**
	 * 读取机构Excel数据
	 * 
	 * @param file
	 * @return List<Organization>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Organization> readOrganizationExcel(String file) throws InvalidFormatException, IOException {
		List<Organization> list = new ArrayList<Organization>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Organization bean = new Organization();
			// 遍历行单元格
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);

				switch (j) {
				case 0:
					bean.setCode(cellValue);
					break;
				case 1:
					bean.setName(cellValue);
					break;
				case 2:
					if (cellValue.indexOf(".") == -1) {
						bean.setRegionCode(cellValue);
					} else {
						bean.setRegionCode(cellValue.substring(0, cellValue.indexOf(".")));
					}
					break;
				case 3:
					bean.setOrganizationCode(cellValue);
					break;
				case 4:
					bean.setEname(cellValue);
					break;
				default:
					System.out.println("organization cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}
	
	public List<Department> readDepartmentExcel(String file) throws InvalidFormatException, IOException {
		List<Department> list = new ArrayList<Department>();
		
		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			
			Department bean = new Department();
			
			// 遍历行单元格
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);
				
				Organization org = new Organization();

				switch (j) {
				case 0:
					//过滤小数点
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					//获取所属父机构
					org.setCode(cellValue.substring(0, cellValue.length()-2));
					bean.setOrganization(org);
					bean.setGradeCode(cellValue);
					break;
				case 1:
					bean.setName(cellValue);
					break;
				case 2:
					if (cellValue.indexOf(".") == -1) {
						bean.setRegionCode(cellValue);
					} else {
						bean.setRegionCode(cellValue.substring(0, cellValue.indexOf(".")));
					}
					break;
				case 3:
					break;
				default:
					System.out.println("organization cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}
	
	/**
	 * 读取人员Excel数据
	 * @param file
	 * @return List<Person>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Person> readPersonExcel(String file) throws InvalidFormatException, IOException {
		List<Person> list = new ArrayList<Person>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Person bean = new Person();
			// 遍历行单元格
			Organization org = new Organization();
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);
				
				switch (j) {
				case 0:
					bean.setName(cellValue);
					break;
				case 1:
					//过滤小数点
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					//获取所属机构
					org.setOrganizationCode(cellValue);
					bean.setOrganization(org);
					break;
				case 2:
					bean.setMobile(cellValue);
					break;
				case 3:
					bean.setEmail(cellValue);
					break;
				case 4:
					org.setName(cellValue);
					bean.setOrganization(org);
					break;
				case 5:
					org.setCode(cellValue);
					bean.setOrganization(org);
					break;
				default:
					System.out.println("person cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}
	
	/**
	 * 读取人员Excel数据(外网)
	 * @param file
	 * @return List<Person>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Person> readPersonExcelNetwork(String file) throws InvalidFormatException, IOException {
		List<Person> list = new ArrayList<Person>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Person bean = new Person();
			// 遍历行单元格
			Organization org = new Organization();
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);
				
				switch (j) {
				case 0:
					bean.setName(cellValue);
					break;
				case 1:
					//过滤小数点
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					//获取所属机构
					org.setOrganizationCode(cellValue);
					bean.setOrganization(org);
					break;
				case 2:
					bean.setMobile(cellValue);
					break;
				case 3:
					bean.setEmail(cellValue);
					break;
				default:
					System.out.println("person cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}
	
	/**
	 * 读取账户Excel数据
	 * @param file
	 * @return List<Person>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Account> readAccountExcel(String file) throws InvalidFormatException, IOException {
		List<Account> list = new ArrayList<Account>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Account bean = new Account();
			//获取所属机构
			Organization org = new Organization();
			// 遍历行单元格
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);

				switch (j) {
				case 0:
					bean.setBid(cellValue);
					break;
				case 1:
					//过滤小数点
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					
					org.setOrganizationCode(cellValue);
					bean.setOrganization(org);
					break;
				case 2:
					//获取关联人员姓名
					Person person = new Person();
					person.setName(cellValue);
					bean.setPerson(person);
					break;
				case 3:
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					
					org.setCode(cellValue);
					bean.setOrganization(org);
					break;
				default:
					System.out.println("account cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}
	
	/**
	 * 读取账户Excel数据(外网)
	 * @param file
	 * @return List<Person>
	 * @throws InvalidFormatException
	 * @throws IOException
	 * @author CY
	 */
	public List<Account> readAccountExcelNew(String file) throws InvalidFormatException, IOException {
		List<Account> list = new ArrayList<Account>();

		Workbook workbook = WorkbookFactory.create(new File(file));
		Sheet sheet = workbook.getSheetAt(0);
		int lastRowNum = sheet.getLastRowNum();
		// 遍历行数
		for (int i = 0; i <= lastRowNum; i++) {
			Row row = sheet.getRow(i);
			int lastCellNum = row.getLastCellNum();
			Account bean = new Account();
			//获取所属机构
			Organization org = new Organization();
			// 遍历行单元格
			for (int j = 0; j < lastCellNum; j++) {

				Cell cell = row.getCell(j);
				String cellValue = this.getCellValue(cell);

				switch (j) {
				case 0:
					bean.setBid(cellValue);
					break;
				case 1:
					//过滤小数点
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					
					org.setOrganizationCode(cellValue);
					bean.setOrganization(org);
					break;
				case 2:
					//获取关联人员姓名
					Person person = new Person();
					person.setName(cellValue);
					bean.setPerson(person);
					break;
				case 3:
					if (cellValue.indexOf(".") != -1) {
						cellValue = cellValue.substring(0, cellValue.indexOf("."));
					}
					if (cellValue != null && "1".equals(cellValue)) {
						bean.setAccountType("ORGANIZE");
					} else {
						bean.setAccountType("PERSON");
					}
					break;
				default:
					System.out.println("account cell value...");
					break;
				}
			}
			list.add(bean);
		}
		return list;
	}

	/**
	 * 由于Excel当中的单元格Cell存在类型,若获取类型错误 就会产生错误, 所以通过此方法将Cell内容全部转换为String类型
	 * 
	 * @param cell
	 * @return String
	 * @author CY 2015-11-20 14:11:21
	 */
	private String getCellValue(Cell cell) {
		String str = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			str = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			str = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			str = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_NUMERIC:
			str = String.valueOf(cell.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_STRING:
			str = String.valueOf(cell.getStringCellValue());
			break;
		default:
			str = null;
			break;
		}
		return str;
	}
}
